package com.yunji.system.config.mybatis;

import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.yunji.common.annotation.DataScopeThread;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import org.springframework.util.CollectionUtils;

import java.text.MessageFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author : peter-zhu
 * @date : 2024/11/23 17:18
 * @description : 可以给查询sql拼接where条件
 **/
public class SqlParseEngine {

    private final Set<String> ignorableTableNames = new HashSet<>();

    private final Set<String> scopeTableNames = new HashSet<>();

    private boolean children;

    private int deep;

    private final List<SqlParseConcat> sqlParseConcatList = new ArrayList<>();

    public static final String BLANK_STR = " ";

    public static final String NULL_STR = "";

    public static final String LEFT_BRACKET = "(";

    public static final String RIGHT_BRACKET = ")";

    public static final String CONCAT_SYMBOL_OR = "or";

    public static final String CONCAT_SYMBOL_AND = "and";

    public SqlParseEngine() {
        this.children = false;
        this.deep = 0;
    }

    public static SqlParseEngine builder(DataScopeThread dataScopeThread) {
        SqlParseEngine sqlParseEngine = new SqlParseEngine();
        sqlParseEngine.children = dataScopeThread.isChildren();
        sqlParseEngine.ignorableTableNames.addAll(Sets.newHashSet(Arrays.asList(dataScopeThread.getExcludeTables())));
        sqlParseEngine.scopeTableNames.addAll(Sets.newHashSet(Arrays.asList(dataScopeThread.getScopeTables())));
        sqlParseEngine.deep = dataScopeThread.getDeep();
        return sqlParseEngine;
    }

    public static SqlParseEngine builder() {
        return new SqlParseEngine();
    }


    public SqlParseEngine concat(SqlParseConcat sqlParseConcat) {
        this.sqlParseConcatList.add(sqlParseConcat);
        return this;
    }

    public SqlParseEngine concat(List<SqlParseConcat> list) {
        this.sqlParseConcatList.addAll(list);
        return this;
    }

    public SqlParseEngine children(boolean children) {
        this.children = children;
        return this;
    }

    public SqlParseEngine deep(int deep) {
        this.deep = deep;
        return this;
    }

    public SqlParseEngine addIgnorableTableNames(String... tableNames) {
        this.ignorableTableNames.addAll(Arrays.asList(tableNames));
        return this;
    }

    public SqlParseEngine build() {
        return this;
    }


    private static String getAliasName(Alias alias) {
        if (alias != null)
            return alias.getName();
        return null;
    }


    private void addConditionToPlainSelect(Select select, int selectDeep) throws JSQLParserException {
        if (select instanceof PlainSelect) {
            selectDeep++;
            PlainSelect plainSelect = (PlainSelect) select;
            FromItem fromItem = plainSelect.getFromItem();
            if (fromItem instanceof Table) {
                Table fromTable = (Table) fromItem;
                addConditionToSelect(plainSelect, fromTable);
            }
            System.out.println("this.deep--------addConditionToPlainSelect--------------------------" + this.deep);
            System.out.println("selectDeep--------addConditionToPlainSelect--------------------------" + selectDeep);
            List<Join> joins = plainSelect.getJoins();
            if (joins != null) {
                for (Join join : joins) {
                    if (join.getRightItem() instanceof Table) {
                        Table joinTable = (Table) join.getFromItem();
                        addConditionToSelect(plainSelect, joinTable);
                        System.out.println("selectDeep------getRightItem----------------------------" + selectDeep);
                    } else if (this.children && this.deep >= selectDeep && join.getRightItem() instanceof ParenthesedSelect) {
                        ParenthesedSelect childSelect = (ParenthesedSelect) join.getFromItem();
                        System.out.println("selectDeep---------children-------------------------" + selectDeep);
                        if (childSelect != null && childSelect.getSelect() != null) {
                            System.out.println("childSelect.getSelect().toString()" + childSelect.getSelect().toString());
                            addConditionToPlainSelect(childSelect.getPlainSelect(), selectDeep);
                        }
                    }
                }
            }
        } else {
            System.out.println(select.toString());
        }
    }

    public PlainSelect addConditionToSql(String sql) throws JSQLParserException {
        Statement sqlStatement = CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = null;
        if (sqlStatement instanceof Select) {
            plainSelect = (PlainSelect) sqlStatement;
            System.out.println("原始sql:" + plainSelect);
            addConditionToPlainSelect(plainSelect, 0);
        }
        return plainSelect;
    }

    protected String getConcatAlias(Table table) {
        if (table == null)
            return "";
        if (table.getAlias() != null) {
            return getAliasName(table.getAlias());
        }
        return table.getName();
    }

    protected void addConditionToSelect(PlainSelect plainSelect, Table table) throws JSQLParserException {
        boolean isMany = this.sqlParseConcatList.size() > 1;
        StringBuilder buildSql = new StringBuilder(isMany ? LEFT_BRACKET : NULL_STR);
        boolean exec = false;
        for (int i = 0; i < this.sqlParseConcatList.size(); i++) {
            SqlParseConcat sqlParseConcat = this.sqlParseConcatList.get(i);
            //表和字段不存在 不加条件
            Boolean exists = SqlParseCacheManager.exists(table.getName(), sqlParseConcat.getColumn());
            if (!exists)
                continue;
            // 忽略不加条件的table表名
            if (this.ignorableTableNames.contains(table.getName()))
                continue;
            // 指定table
            if (!CollectionUtils.isEmpty(this.scopeTableNames) && !this.scopeTableNames.contains(table.getName()))
                continue;
            if (i != 0 && sqlParseConcat.getSymbol() != null)
                buildSql.append(BLANK_STR).append(sqlParseConcat.getSymbol()).append(BLANK_STR);
            buildSql.append(sqlParseConcat.getConcatSql());
            exec = true;
        }
        if (isMany)
            buildSql.append(RIGHT_BRACKET);
        if (exec) {
            // 给当前查询添加 WHERE 条件
            Expression newCondition = CCJSqlParserUtil.parseCondExpression(convertCondition(buildSql.toString(), getConcatAlias(table)));
            if (plainSelect.getWhere() != null) {
                plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), newCondition));
            } else {
                plainSelect.setWhere(newCondition);
            }
        }
    }

    private static String convertCondition(String concatSql, String concatAlias) {
        int placeholderCount = countPlaceholders(concatSql);
        String[] placeholders = generatePlaceholders(placeholderCount, concatAlias);
        if (placeholders.length == 0)
            return concatSql;
        return MessageFormat.format(concatSql, (Object[]) placeholders);
    }

    public static int countPlaceholders(String template) {
        Pattern pattern = Pattern.compile("\\{\\d+}");
        Matcher matcher = pattern.matcher(template);
        int count = 0;
        while (matcher.find()) {
            count++;
        }
        return count;
    }

    public static String[] generatePlaceholders(int count, String value) {
        String[] placeholders = new String[count];
        Arrays.fill(placeholders, value);
        return placeholders;
    }

    public static class tableInfo {
        public static Map<String, String> getTableMap(String sql, boolean children) throws JSQLParserException {
            Map<String, String> map = Maps.newHashMap();
            Statement sqlStatement = CCJSqlParserUtil.parse(sql);
            System.out.println(sqlStatement.toString());

            PlainSelect plainSelect = null;
            if (sqlStatement instanceof PlainSelect) {
                plainSelect = (PlainSelect) sqlStatement;
            } else if (sqlStatement instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) sqlStatement;
                plainSelect = setOperationList.getPlainSelect();
            } else
                return map;
            FromItem fromItem = plainSelect.getFromItem();
            if (fromItem instanceof Table) {
                Table fromTable = (Table) fromItem;
                map.put(fromTable.getName(), getAliasName(fromItem.getAlias()));
            } else if (fromItem instanceof ParenthesedSelect) {
                ParenthesedSelect childSelect = (ParenthesedSelect) fromItem;
                Map<String, String> tableAndAlias = getTableMap(childSelect.getSelect().toString(), true);
                map.putAll(tableAndAlias);
            }
            List<Join> joins = plainSelect.getJoins();
            if (joins != null) {
                for (Join join : joins) {
                    if (join.getRightItem() instanceof Table) {
                        Table f = (Table) join.getFromItem();
                        map.put(f.getName(), getAliasName(f.getAlias()));
                    } else if (children && join.getRightItem() instanceof ParenthesedSelect) {
                        ParenthesedSelect childSelect = (ParenthesedSelect) join.getFromItem();
                        Map<String, String> tableAndAlias = getTableMap(childSelect.getSelect().toString(), true);
                        map.putAll(tableAndAlias);
                    }
                }
            }
            return map;
        }

        public static Map<String, String> getTableMap(String sql) throws JSQLParserException {
            return getTableMap(sql, false);
        }
    }
}
